Re: index file bloating still in 7.4 ? - Mailing list pgsql-performance

Hi Tom, Josh,

We tried one more thing: with the table not being updated
at all and we did vacuum. Each time a vacuum is done,
the index file becomes bigger.

This is probably what is contributing to the index file
growing as well.

Thanks.

Gan

At 11:04 am -0500 2003/10/20, Seum-Lim Gan wrote:
>Hi Josh, Tom,
>
>OK. As I understand it, vacuum does not release the space
>used by the index file.
>However, it should be able to reuse the space for indexing.
>
>I have observed that during initial updates of the table,
>the index file did not grow and was steady but it did not last long
>and keeps growing afterwards. Vacuum/vacuum analyze did not help.
>
>In all the update testing, vacuum analyze was done every 1 minute.
>
>Tom, something caught your attention the last time.
>
>Any insight so far ? Is it a bug ?
>
>Thanks.
>
>Gan
>
>Tom Lane wrote:
>
>Seum-Lim Gan <slgan@lucent.com> writes:
>>  vacuum verbose analyze dsperf_rda_or_key;
>>  INFO:  vacuuming "scncraft.dsperf_rda_or_key"
>>  INFO:  index "dsperf242_1105" now contains 300000 row versions in
>>12387 pages
>>  DETAIL:  3097702 index row versions were removed.
>>  0 index pages have been deleted, 0 are currently reusable.
>
>Hm, interesting that you deleted 90% of the entries and still had no
>empty index pages at all.  What was the pattern of your deletes and/or
>updates with respect to this index's key?
>
>>  However, when I check the disk space usage, it has not changed.
>
>It won't in any case.  Plain VACUUM is designed for maintaining a
>steady-state level of free space in tables and indexes, not for
>returning major amounts of space to the OS.  For that you need
>more-invasive operations like VACUUM FULL or REINDEX.
>
>            regards, tom lane
>
>At 12:04 pm -0700 2003/10/19, Josh Berkus wrote:
>>Gan,
>>
>>>  Oh, so in order to reclaim the disk space, we must run
>>>  reindex or vacuum full ?
>>>  This will lock out the table and we won't be able to do anything.
>>>  Looks like this is a problem. It means we cannot use it for
>>>  24x7 operations without having to stop the process and do the vacuum full
>>>  and reindex. Is there anything down the road that these operations
>>>  will not lock out the table ?
>>
>>I doubt it; the amount of page-shuffling required to reclaim 90% of the space
>>in an index for a table that has been mostly cleared is substantial, and
>>would prevent concurrent access.
>>
>>Also, you seem to have set up an impossible situation for VACUUM.   If I'm
>>reading your statistics right, you have a large number of threads accessing
>>most of the data 100% of the time, preventing VACUUM from cleaning up the
>>pages.    This is not, in my experience, a realistic test case ... there are
>>peak and idle periods for all databases, even webservers that have been
>>slashdotted.
>>
>>--
>>Josh Berkus
>>Aglio Database Solutions
>>San Francisco
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>--
>+--------------------------------------------------------+
>| Seum-Lim GAN                 email : slgan@lucent.com  |
>| Lucent Technologies                                    |
>| 2000 N. Naperville Road, 6B-403F  tel : (630)-713-6665 |
>| Naperville, IL 60566, USA.        fax : (630)-713-7272 |
>|       web : http://inuweb.ih.lucent.com/~slgan         |
>+--------------------------------------------------------+
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html


--
+--------------------------------------------------------+
| Seum-Lim GAN                 email : slgan@lucent.com  |
| Lucent Technologies                                    |
| 2000 N. Naperville Road, 6B-403F  tel : (630)-713-6665 |
| Naperville, IL 60566, USA.        fax : (630)-713-7272 |
|       web : http://inuweb.ih.lucent.com/~slgan         |
+--------------------------------------------------------+